<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

Class Mdevice extends MY_Model {

    public function __construct() {
        parent::__construct();
    }

    public function getDataDeviceEdit($id) {
        $data = $this->db->query('SELECT d.*, (select id from tbl_device where id > d.id ORDER BY id ASC limit 1) AS next,(select id from tbl_device where id < d.id ORDER BY id DESC limit 1) AS previours, DATE_FORMAT(FROM_UNIXTIME(d.time_update), "%H giờ, %i phút, %s giây - ngày %d, tháng %m, năm %Y.") AS date_update, a.`long`, a.lat,CONCAT(a.address, " - ",dt.`name`, " - ", p.`name`) AS address FROM tbl_device d INNER JOIN tbl_address a ON d.id_address = a.id INNER JOIN province p ON a.id_province = p.provinceid INNER JOIN district dt ON a.id_district = dt.districtid WHERE md5(d.id) = "' . $id . '"')->row_object();
        $data->comment = $this->db->query('SELECT * FROM comment WHERE md5(device_id) = "' . $id . '"')->result_object();
        $this->db_b = $this->load->database('radiusdb', TRUE);
        $data->statistic = $this->db_b->query('SELECT SUM(ra.acctinputoctets) AS indata, SUM(ra.acctoutputoctets) AS outdata, DATE_FORMAT(ra.acctstarttime,"%m/%Y") AS times FROM radacct ra WHERE username IN (SELECT username FROM radcheck WHERE md5(id_device) = "' . $id . '") GROUP BY times')->result_object();

        return $data;
    }

    public function getStatisticDeviceAjaxDB($id) {
        $this->db_b = $this->load->database('radiusdb', TRUE);
        return $this->db_b->query('SELECT SUM(ra.acctinputoctets) AS indata, SUM(ra.acctoutputoctets) AS outdata, DATE_FORMAT(ra.acctstarttime,"%m/%Y") AS times FROM radacct ra WHERE username IN (SELECT username FROM radcheck WHERE md5(id_device) = "' . $id . '") GROUP BY times')->result_object();
    }

    public function resetdeviceajaxDB($id) {
        $this->db->query('UPDATE tbl_device SET reset_device = ' . time() . ' WHERE md5(id) = "' . $id . '"');
    }

    public function addnotedeviceajaxDB($id, $content) {
        $this->saveComment($this->db->query('SELECT id FROM tbl_device WHERE md5(id) = "' . $id . '"')->row_object()->id, $content);
    }

    public function gethistorydeviceajaxDB($id) {
        return $this->db->query('SELECT * FROM comment WHERE device_id = ' . $this->db->query('SELECT id FROM tbl_device WHERE md5(id) = "' . $id . '"')->row_object()->id . ' ORDER BY id DESC')->result_object();
    }

    public function deleteDeviceDB($id) {
        $info = $this->db->query('SELECT id, linkupdate FROM tbl_device WHERE md5(id) = "' . $id . '"')->row_object();
        $id_device = $info->id;
        $linkupdate = $info->linkupdate;
        $this->db->delete('comment', "device_id = " . $id_device);
        $this->db->delete('tbl_device', "id = " . $id_device);

        $deviceid = $this->db->query("SELECT rp2.username FROM cpanel_radius.radcheck rp2 WHERE rp2.id ='.$id_device.'")->result_object();
        $iddevices = '';
        foreach ($deviceid AS $el => $le) {
            $iddevices .= ',"' . $le->username . '"';
        }

        $this->query('DELETE FROM cpanel_radius.radreply WHERE username IN (' . $iddevices . ')');
        $this->query('DELETE FROM cpanel_radius.radcheck WHERE id_device = ' . $id_device);

        unlink(PROJECT_PATH . '/public/' . $linkupdate);
    }

    public function saveDevice($fileUpload) {
        $arr = array(
            'id_address' => $this->getParamInt('place'),
            'namedevice' => $this->getParamString('namedevice'),
            'mac' => $this->getParamString('mac'),
            'nasid' => $this->getParamString('nasid'),
            'created' => date('Y-m-d H:i:s'),
            'linkupdate' => $fileUpload,
            'version' => $this->getParamString('version'),
            'active' => $this->getParamInt('active'),
            'network_group' => $this->getParamInt('network_group'),
            'HS_DEFSESSIONTIMEOUT' => $this->getParamString('HS_DEFSESSIONTIMEOUT'),
            'HS_DEFIDLETIMEOUT' => $this->getParamString('HS_DEFIDLETIMEOUT'),
            'HS_UAMFORMAT' => $this->getParamString('HS_UAMFORMAT'),
            'HS_UAMHOMEPAGE' => $this->getParamString('HS_UAMHOMEPAGE'),
            'id_created' => $this->session->userdata('id')
        );
        $this->db->insert('tbl_device', $arr);
        $id = $this->db->insert_id();

        if (in_array($fileUpload, array(null, '', '0'))) {
            $this->genConfig($arr['nasid'], $id, $arr['mac'], $arr['HS_DEFSESSIONTIMEOUT'], $arr['HS_DEFIDLETIMEOUT'], $arr['HS_UAMFORMAT'], $arr['HS_UAMHOMEPAGE'], 1);
        } else {
            $this->genConfigUploadfie($id, $arr['mac'], $arr['linkupdate']);
        }

        // Modify Radius fuckyou. haha
        $this->createAccountRadius($id);

        // Save Comment Device
        $content = $this->getParamString('content');
        if (!in_array($content, array(null, '0', ''))) {
            $this->saveComment($id, $content);
        }

        // sync wicity
        $this->syncAddressDevice(array('nas_id' => $arr['nasid']), $arr['id_address']);

        return true;
    }

    private function syncEditAddressDevice($ds, $idplace) {
       // fuck connect db radius
        $this->db_wicity = $this->load->database('wicitydb', TRUE);
        $data_account = array();

        $data = $this->db->query('SELECT `name`, address, `lat`, `long`, id_province, id_district FROM tbl_address WHERE id = ' . $idplace)->row_array();

        $place_wicity = $this->db_wicity->query('SELECT id FROM place WHERE id_address_sync = ' . $idplace);
        if ($place_wicity->num_rows() == 0) {
            $dr = array(
                'id_address_sync' => $idplace,
                'name' => $data['name'],
                'address' => $data['address'],
                'lat' => $data['lat'],
                'long' => $data['long'],
                'province_id' => $data['id_province'],
                'district_id' => $data['id_district']
            );

            $this->db_wicity->insert('place', $dr);
            $id = $this->db_wicity->insert_id();
        } else {
            $dr = array(
                'name' => $data['name'],
                'address' => $data['address'],
                'lat' => $data['lat'],
                'long' => $data['long'],
                'province_id' => $data['id_province'],
                'district_id' => $data['id_district']
            );
            $id = $place_wicity->row_object()->id;
            $this->db_wicity->update('place', $dr, "id = " . $id);
        }

        $nasid_wicity = $this->db_wicity->query('SELECT id FROM modem WHERE nas_id = "' . $ds['nas_id'] . '"');
        if ($nasid_wicity->num_rows() == 0) {
            $dr = array(
                'nas_id' => $ds['nas_id'],
                'name' => $data['name'],
                'id_place' => $id
            );
            $this->db_wicity->insert('modem', $dr);
        }else{
            $dr = array(
                'name' => $data['name'],
                'id_place' => $id
            );
            $this->db_wicity->update('modem', $dr, "id = ".$nasid_wicity->row_object()->id);
        }
    }

    private function syncAddressDevice($ds, $idplace) {
        // fuck connect db radius
        $this->db_wicity = $this->load->database('wicitydb', TRUE);
        $data_account = array();

        $data = $this->db->query('SELECT `name`, address, `lat`, `long`, id_province, id_district FROM tbl_address WHERE id = ' . $idplace)->row_array();

        $place_wicity = $this->db_wicity->query('SELECT id FROM place WHERE id_address_sync = ' . $idplace);
        if ($place_wicity->num_rows() == 0) {
            $dr = array(
                'id_address_sync' => $idplace,
                'name' => $data['name'],
                'address' => $data['address'],
                'lat' => $data['lat'],
                'long' => $data['long'],
                'province_id' => $data['id_province'],
                'district_id' => $data['id_district']
            );

            $this->db_wicity->insert('place', $dr);
            $id = $this->db_wicity->insert_id();
        } else {
            $dr = array(
                'name' => $data['name'],
                'address' => $data['address'],
                'lat' => $data['lat'],
                'long' => $data['long'],
                'province_id' => $data['id_province'],
                'district_id' => $data['id_district']
            );
            $id = $place_wicity->row_object()->id;
            $this->db_wicity->update('place', $dr, "id = " . $id);
        }

        $nasid_wicity = $this->db_wicity->query('SELECT id FROM modem WHERE nas_id = "' . $ds['nas_id'] . '"');
        if ($nasid_wicity->num_rows() == 0) {
            $dr = array(
                'nas_id' => $ds['nas_id'],
                'name' => $data['name'],
                'id_place' => $id
            );
            $this->db_wicity->insert('modem', $dr);
        }else{
            $dr = array(
                'name' => $data['name'],
                'id_place' => $id
            );
            $this->db_wicity->update('modem', $dr, "id = ".$nasid_wicity->row_object()->id);
        }
    }

    private function genConfigUploadfie($id_device, $macdevice, $linkupdate) {
        $link_game_online = $this->extralfileziptoserver(PROJECT_PATH . '/public/' . $linkupdate);
        /* File config update php */
        $configcheckupdate = file_get_contents($link_game_online['path'] . '/update/checkupdate.php');
        $_exp = explode('//start', $configcheckupdate);
        $_st1 = $_exp[0];
        $_expl2 = explode('//end mac', $_exp[1]);
        $_str3 = "\n\r//end mac" . $_expl2[1];
        $_id_device = '$id_device = ' . $id_device . ';$mac = "' . $macdevice . '";';
        file_put_contents($link_game_online['path'] . '/update/checkupdate.php', $_st1 . "\n\r" . '//start' . "\n\r" . $_id_device . $_str3);

        $this->load->library('zip');
        $this->zip->read_dir('public/tmpsconfig/' . $link_game_online['time'] . '/update', FALSE);

        if ($type == 1) {
            $this->zip->archive('public/' . $linkupdate);
        } else {
            $this->zip->archive('public/' . $linkupdate);
        }

        //@unlink(PROJECT_PATH . '/public/tmpsconfig/' . $link_game_online['time']);
    }

    private function genConfig($nasid, $id_device, $macdevice, $HS_DEFSESSIONTIMEOUT, $HS_DEFIDLETIMEOUT, $HS_UAMFORMAT, $HS_UAMHOMEPAGE, $type, $file_config = '') {
        if ($type == 1) {
            $link_game_online = $this->extralfileziptoserver(PROJECT_PATH . '/resource/config.zip');
        } else {
            $link_game_online = $this->extralfileziptoserver(PROJECT_PATH . '/public/' . $file_config->linkupdate);
        }


        /* File config */
        $config = file_get_contents($link_game_online['path'] . '/update/config');
        $exp = explode('HS_NASID=', $config);
        $st1 = $exp[0];
        $expl2 = explode('HS_RADIUS=', $exp[1]);
        $str3 = "\n\rHS_RADIUS=" . $expl2[1];
        file_put_contents($link_game_online['path'] . '/update/config', $st1 . "\n\r" . 'HS_NASID=' . $nasid . $str3);

        /* File config HS_DEFSESSIONTIMEOUT */
        $configDEFSESSIONTIMEOUT = file_get_contents($link_game_online['path'] . '/update/config');
        $_expDEFSESSIONTIMEOUT = explode('HS_DEFSESSIONTIMEOUT=', $configDEFSESSIONTIMEOUT);
        $st1_HS_DEFSESSIONTIMEOUT = $_expDEFSESSIONTIMEOUT[0];
        $exp2HS_DEFSESSIONTIMEOUT = explode('# Default session-timeout if not defined by RADIUS (0 for unlimited)', $_expDEFSESSIONTIMEOUT[1]);
        $str3HS_DEFSESSIONTIMEOUT = "\n\r# Default session-timeout if not defined by RADIUS (0 for unlimited)" . $exp2HS_DEFSESSIONTIMEOUT[1];
        file_put_contents($link_game_online['path'] . '/update/config', $st1_HS_DEFSESSIONTIMEOUT . "\n\r" . 'HS_DEFSESSIONTIMEOUT=' . $HS_DEFSESSIONTIMEOUT . $str3HS_DEFSESSIONTIMEOUT);

        /* File config HS_DEFIDLETIMEOUT */

        $configDEFIDLETIMEOUT = file_get_contents($link_game_online['path'] . '/update/config');
        $_expHS_DEFIDLETIMEOUT = explode('HS_DEFIDLETIMEOUT=', $configDEFIDLETIMEOUT);
        $st1_HS_DEFIDLETIMEOUT = $_expHS_DEFIDLETIMEOUT[0];
        $exp2HS_HS_DEFIDLETIMEOUT = explode('# Default idle-timeout if not defined by RADIUS (0 for unlimited)', $_expHS_DEFIDLETIMEOUT[1]);
        $str3HS_DEFIDLETIMEOUT = "\n\r# Default idle-timeout if not defined by RADIUS (0 for unlimited)" . $exp2HS_HS_DEFIDLETIMEOUT[1];
        file_put_contents($link_game_online['path'] . '/update/config', $st1_HS_DEFIDLETIMEOUT . "\n\r" . 'HS_DEFIDLETIMEOUT=' . $HS_DEFIDLETIMEOUT . $str3HS_DEFIDLETIMEOUT);

        /* File config HS_UAMFORMAT */

        $configUAMFORMAT = file_get_contents($link_game_online['path'] . '/update/config');
        $_expHS_UAMFORMAT = explode('HS_UAMFORMAT=', $configUAMFORMAT);
        $st1_HS_UAMFORMAT = $_expHS_UAMFORMAT[0];
        $exp2HS_UAMFORMAT = explode('#   Same principal goes for HS_UAMHOMEPAGE.', $_expHS_UAMFORMAT[1]);
        $str3HS_UAMFORMAT = "\n\r#   Same principal goes for HS_UAMHOMEPAGE." . $exp2HS_UAMFORMAT[1];
        file_put_contents($link_game_online['path'] . '/update/config', $st1_HS_UAMFORMAT . "\n\r" . 'HS_UAMFORMAT=' . $HS_UAMFORMAT . $str3HS_UAMFORMAT);

        /* File config HS_UAMHOMEPAGE */

        $configUAMHOMEPAGE = file_get_contents($link_game_online['path'] . '/update/config');
        $_expHS_UAMHOMEPAGE = explode('HS_UAMHOMEPAGE=', $configUAMHOMEPAGE);
        $st1_HS_UAMHOMEPAGE = $_expHS_UAMHOMEPAGE[0];
        $exp2HS_UAMHOMEPAGE = explode('#   This option will be configured to be the WISPr LoginURL as well', $_expHS_UAMHOMEPAGE[1]);
        $str3HS_UAMHOMEPAGE = "\n\r#   This option will be configured to be the WISPr LoginURL as well" . $exp2HS_UAMHOMEPAGE[1];
        file_put_contents($link_game_online['path'] . '/update/config', $st1_HS_UAMHOMEPAGE . "\n\r" . 'HS_UAMHOMEPAGE=' . $HS_UAMHOMEPAGE . $str3HS_UAMHOMEPAGE);


        /* File config update php */
        $configcheckupdate = file_get_contents($link_game_online['path'] . '/update/checkupdate.php');
        $_exp = explode('//start', $configcheckupdate);
        $_st1 = $_exp[0];
        $_expl2 = explode('//end mac', $_exp[1]);
        $_str3 = "\n\r//end mac" . $_expl2[1];
        $_id_device = '$id_device = ' . $id_device . ';$mac = "' . $macdevice . '";';
        file_put_contents($link_game_online['path'] . '/update/checkupdate.php', $_st1 . "\n\r" . '//start' . "\n\r" . $_id_device . $_str3);

        $this->load->library('zip');
        $this->zip->read_dir('public/tmpsconfig/' . $link_game_online['time'] . '/update', FALSE);

        if ($type == 1) {
            $this->zip->archive('public/' . md5($id_device) . '.zip');
            $this->db->query('UPDATE tbl_device set linkupdate = "' . md5($id_device) . '.zip" WHERE id = "' . $id_device . '"');
        } else {
            $this->zip->archive('public/' . $file_config->linkupdate);
            $this->db->query('UPDATE tbl_device set version = "' . (($file_config->version) + 1) . '" WHERE id = "' . $id_device . '"');
        }

        @unlink(PROJECT_PATH . '/public/tmpsconfig/' . $link_game_online['time']);
    }

    private function saveComment($id, $content) {
        $arr = array(
            'device_id' => $id,
            'content' => $content,
            'admin_id' => $this->session->userdata('id'),
            'created' => date('Y/m/d H:i:s'),
            'active' => 1
        );
        $this->db->insert('comment', $arr);
    }

    public function checkIsUniqueMacNasid($mac, $nasid, $id) {
        $ds = $this->db->query('SELECT mac, nasid FROM tbl_device WHERE md5(id) = "' . $id . '"')->row_object();
        $array = array('mac' => false, 'nasid' => false);
        if ($mac == $ds->mac) {
            $array['mac'] = true;
        }
        if ($nasid == $ds->nasid) {
            $array['nasid'] = true;
        }
        return $array;
    }

    private function createAccountRadius($id) {
        // fuck connect db radius
        $this->db_radius = $this->load->database('radiusdb', TRUE);
        // array account radius of device
        $data_account = array();
        for ($i = 1; $i <= 6; $i++) {
            $data_account[] = array(
                'username' => 'account' . $id . $i,
                'attribute' => 'Cleartext-Password',
                'op' => ':=',
                'value' => $this->generateCode(6),
                'id_device' => $id
            );
        }
        $this->db_radius->insert_batch('radcheck', $data_account);


        // array bandwidth and time radius of device
        $data_radreply = array();
        $cv = 0;
        $time = array(3600, 7200, 10800, 14400, 18000, 21600);
        $stt_time = 0;
        for ($iz = 1; $iz <= 6; $iz++) {
            $data_radreply[$cv] = array(
                'username' => 'account' . $id . $iz,
                'attribute' => 'WISPr-Bandwidth-Max-Down',
                'op' => ':=',
                'value' => '1000000000000000000'
            );
            $cv++;
            $data_radreply[$cv] = array(
                'username' => 'account' . $id . $iz,
                'attribute' => 'WISPr-Bandwidth-Max-Up',
                'op' => ':=',
                'value' => '1000000000000000000'
            );
            $cv++;

            $data_radreply[$cv] = array(
                'username' => 'account' . $id . $i,
                'attribute' => 'Session-Timeout',
                'op' => ':=',
                'value' => $time[$stt_time]
            );
            $cv++;
            $stt_time++;
        }

        $this->db_radius->insert_batch('radreply', $data_radreply);
        return true;
        // end proccess
    }

    public function editDevice($id, $fileUpload) {
        $arr = array(
            'namedevice' => $this->getParamString('namedevice'),
            'mac' => $this->getParamString('mac'),
            'nasid' => $this->getParamString('nasid'),
            'version' => $this->getParamString('version'),
            'active' => $this->getParamInt('active'),
            'HS_DEFSESSIONTIMEOUT' => $this->getParamString('HS_DEFSESSIONTIMEOUT'),
            'HS_DEFIDLETIMEOUT' => $this->getParamString('HS_DEFIDLETIMEOUT'),
            'HS_UAMFORMAT' => $this->getParamString('HS_UAMFORMAT'),
            'HS_UAMHOMEPAGE' => $this->getParamString('HS_UAMHOMEPAGE'),
        );

        if ($this->session->userdata('level') != 3) {
            $arr['type'] = $this->getParamInt('types');
            $arr['network_group'] = $arr['type'];
            $arr['id_address'] = $this->getParamInt('place');
        }

        $ds = $this->db->query('SELECT id,linkupdate,version,nasid,mac,HS_DEFSESSIONTIMEOUT,HS_DEFIDLETIMEOUT,HS_UAMFORMAT,HS_UAMHOMEPAGE FROM tbl_device WHERE md5(id) = "' . $id . '"')->row_object();

        if (($ds->nasid != $arr['nasid']) || ($ds->mac != $arr['mac']) || ($ds->HS_DEFSESSIONTIMEOUT != $arr['HS_DEFSESSIONTIMEOUT']) || ($ds->HS_UAMFORMAT != $arr['HS_UAMFORMAT']) || ($ds->HS_UAMHOMEPAGE != $arr['HS_UAMHOMEPAGE'])) {
            $this->genConfig($arr['nasid'], $ds->id, $arr['mac'], $arr['HS_DEFSESSIONTIMEOUT'], $arr['HS_DEFIDLETIMEOUT'], $arr['HS_UAMFORMAT'], $arr['HS_UAMHOMEPAGE'], 2, $ds);
        }

        if ($fileUpload != null) {
            $arr['linkupdate'] = $fileUpload;
            unlink(PROJECT_PATH . '/public/' . $ds->linkupdate);
            $this->genConfigUploadfie($ds->id, $arr['mac'], $arr['linkupdate']);
        }

        $id_place_sync = @$arr['id_address'] ? @$arr['id_address'] : $this->db->query('SELECT id_address FROM tbl_device WHERE md5(id) = "'.$id.'"')->row_object()->id_address;
        // sync wicity edit
        $this->syncEditAddressDevice(array('nas_id' => $arr['nasid']), $id_place_sync);
        $this->db->update('tbl_device', $arr, "md5(id) = '" . $id . "'");
        return true;
    }

    public function deleteDeviceInfo($id) {
        return $this->db->delete('tbl_device', "id = " . $id);
    }

    public function getDevice($number = 0, $offset = 0) {

        $id = $this->query('SELECT d.id FROM ' . $this->cpanel . '.tbl_device d WHERE d.id not in (SELECT id_device FROM ' . $this->radius . '.radcheck WHERE id_device is not null) GROUP BY d.id')->result_object();

        foreach ($id AS $el => $le) {
            $this->createAccountRadius($le->id);
        }

        $limit = $offset ? intval($offset) : '0';

        $where = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND (radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" )';
        }

//        $network_group = $this->getParamInt('network_group');
//        if (!in_array($network_group, array(null, '0', ''))) {
//            $where .= ' AND a.group_id = ' . $network_group;
//        }
        // Đéo biết cần ko nữa.

        $province_search = $this->getParamInt('province_search');
        if (!in_array($province_search, array(null, '0', ''))) {
            $where .= ' AND p.provinceid = ' . $province_search;
        }

        $district_search = $this->getParamInt('district_search');
        if (!in_array($district_search, array(null, '0', ''))) {
            $where .= ' AND dt.districtid = ' . $district_search;
        }

        $place_search = $this->getParamInt('place_search');
        if (!in_array($place_search, array(null, '0', ''))) {
            $where .= ' AND a.id = ' . $place_search;
        }


        $sql = "SELECT dt.`name` AS dname, 
                        p.`name` AS pname, 
                        d.nasid, 
                        d.active, 
                        from_unixtime(d.time_update, '%H:%i:%s %m/%d/%Y') AS lastonline,
                        d.id AS did, 
                        a.address, 
                        d.namedevice, 
                        SUM(rd.acctinputoctets + rd.acctoutputoctets) AS bw, COUNT(DISTINCT rd.callingstationid) as user
                FROM " . $this->cpanel . ".tbl_device d INNER JOIN " . $this->cpanel . ".tbl_address a ON d.id_address = a.id
                         INNER JOIN " . $this->cpanel . ".province p ON a.id_province = p.provinceid
                         INNER JOIN " . $this->cpanel . ".district dt ON a.id_district = dt.districtid
                         LEFT JOIN " . $this->radius . ".radcheck rc ON d.id = rc.id_device
                         LEFT JOIN " . $this->radius . ".radacct rd ON rc.username = rd.username";

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN " . $this->cpanel . ".manage_device_key mdk ON d.id = mdk.device_id ";
        }
        $sql .= " WHERE 1=1 " . $where;
        if ($this->session->userdata('level') == 2) {

            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND d.id IN (" . $id_dv . ") GROUP BY d.id ";
        }

        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id') . " GROUP BY d.id ";
        }

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " ORDER BY d.namedevice ASC";
        } else {
            $sql .= " GROUP BY d.id ORDER BY d.namedevice ASC";
        }
        //echo $sql;
        $count = $this->query($sql)->num_rows();
        $xdata = " LIMIT " . $limit . "," . $number . " ";
        $xs = $this->query($sql . $xdata)->result_object();

        $cdf = (array) $xs;
        foreach ($cdf AS $ol => $lo) {
            $cdf[$ol]->admin = $this->db->query('SELECT u1.username, 
                                                        u1.id
                                                FROM manage_device_key mdk INNER JOIN `user` u1 ON mdk.user_id = u1.id
                                                WHERE device_id = ' . $lo->did)->result_object();
        }

        return array('data' => $xs, 'count' => $count);
    }

    public function getDeviceInfo($id) {
        return $this->db->query('SELECT * FROM tbl_device WHERE id =' . $id)->row_object();
    }

    public function getListDistrict($id) {
        $data = $this->db->query('SELECT * FROM district WHERE provinceid = ' . $id . ' ORDER BY name ASC')->result_array();

        $arr = array();
        foreach ($data AS $el => $le) {
            $arr[$le['districtid']] = $le['type'] . ' ' . $le['name'];
        }
        return $arr;
    }

    public function updatestatusDB() {
        $this->db->update('tbl_device', array('time_update' => time(), 'warning' => 0), "mac = '" . $this->getParamString('id') . "'");
    }

    public function getPlaceSelect() {
        $data = $this->db->query('SELECT a.id, CONCAT(a.address," - ",d.`name`," - ",p.`name`) as address FROM tbl_address AS a INNER JOIN district AS d ON d.districtid = a.id_district INNER JOIN province AS p ON p.provinceid = a.id_province ORDER BY a.address DESC')->result_array();

        $arr = array();
        $arr[0] = 'Vui lòng chọn địa điểm';
        foreach ($data AS $el => $le) {
            $arr[$le['id']] = $le['address'];
        }
        return $arr;
    }

    public function getMapdata() {
        return $this->db->query('SELECT a.lat, a.`long`, a.id, CONCAT(a.address, " - ",d.`name`, " - ", p.`name`) AS address FROM tbl_address AS a INNER JOIN district AS d ON d.districtid = a.id_district INNER JOIN province AS p ON p.provinceid = a.id_province WHERE a.id = ' . $this->getParamInt("id") . ' ORDER BY a.address DESC')->row_object();
    }

    public function getGroup() {

        return $this->db->query('SELECT id, name FROM tbl_group WHERE 1=1')->result_object();
    }

    public function getNetWorkGroup($id) {

        if ($this->session->userdata('level') == 2) {
            $data = $this->db->query('SELECT tg.id, tg.name FROM tbl_group AS tg LEFT JOIN manage_group_key AS gk ON gk.group_id = tg.id WHERE gk.user_id = ' . $this->session->userdata('id'))->result_array();
        } else if ($this->session->userdata('level') == 3) {
            $data = $this->db->query('SELECT tg.id,tg.`name` FROM tbl_group tg INNER JOIN manage_device_key mdk ON tg.id = mdk.group_id WHERE mdk.user_id = ' . $this->session->userdata('id') . ' GROUP BY tg.id')->result_array();
        } else {
            $data = $this->db->query('SELECT id,`name` FROM tbl_group ORDER BY id DESC')->result_array();
        }

        $arr = array();
        $arr[0] = 'Vui lòng chọn nhóm mạng';
        foreach ($data AS $el => $le) {
            $arr[$le['id']] = $le['name'];
        }
        return $arr;
    }

}
